package com.mms.utsc.smartnet;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import com.mms.utsc.smartnet.Announcement.AnnouncementType;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class LocalDBHandler {

	public static final String KEY_ANNOUNCEMENTID = "aID";
	// if a different user logs onto the same phone, we just recreate the
	// database
	// public static final String KEY_USERID = "userID";
	public static final String KEY_ANNOUNCEMENT_CONTENT = "content";
	public static final String KEY_ANNOUNCEMENT_TYPE = "type";
	public static final String KEY_ANNOUNCEMENT_TITLE = "title";
	public static final String KEY_ANNOUNCEMENT_IMPORTANT = "important";
	// the 2 time stamps will be saved as Date.getTime()
	// time that the announcement was published
	public static final String KEY_ANNOUNCEMENT_TIME = "time";
	// time that the announcement expires
	public static final String KEY_ANNOUNCEMENT_EXPIRE = "expire";

	public static final String SCHED_TABLE = "schedules_table";
	public static final String KEY_SCHED_ID = "sched_id";
	public static final String KEY_SCHED_START = "start";
	public static final String KEY_SCHED_END = "end";
	public static final String KEY_SCHED_COURSECODE = "coursecode";
	public static final String KEY_SCHED_DAY = "day";

	private static final String DATABASE_NAME = "smartnet_local_db";
	private static final String ANNOUNCEMENT_TABLE = "announcementsTable";
	private static final int DATABASE_VERSION = 2;

	private DbHelper ourHelper;
	private final Context ourContext; // Context for the DbHelper class
	private SQLiteDatabase ourDatabase;

	private static class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		/**
		 * called only when the database is first created
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL("CREATE TABLE " + ANNOUNCEMENT_TABLE + " (" + KEY_ANNOUNCEMENTID + " INTEGER NOT NULL, " + KEY_ANNOUNCEMENT_CONTENT + " TEXT NOT NULL, " + KEY_ANNOUNCEMENT_TYPE
					+ " INTEGER NOT NULL, " + KEY_ANNOUNCEMENT_TIME + " INTEGER NOT NULL, " + KEY_ANNOUNCEMENT_EXPIRE + " INTEGER NOT NULL, " + KEY_ANNOUNCEMENT_TITLE + " TEXT NOT NULL, "
					+ KEY_ANNOUNCEMENT_IMPORTANT + " INTEGER NOT NULL);");
			db.execSQL("CREATE TABLE " + SCHED_TABLE + " (" + KEY_SCHED_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_SCHED_START + " TEXT NOT NULL, " + KEY_SCHED_END + " TEXT NOT NULL, "
					+ KEY_SCHED_COURSECODE + " TEXT NOT NULL, " + KEY_SCHED_DAY + " TEXT NOT NULL);");
		}

		/**
		 * Called when we upgrade the database
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

			// if the table exists, we drop it and create a new one
			db.execSQL("DROP TABLE IF EXISTS " + ANNOUNCEMENT_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + SCHED_TABLE);
			onCreate(db);
		}
	}

	public LocalDBHandler(Context c) {
		ourContext = c;
	}

	/**
	 * open database
	 * 
	 * @return the LocalDBHandler object with an opened database
	 * @throws SQLException
	 */
	public LocalDBHandler open() throws SQLException {
		ourHelper = new DbHelper(ourContext);
		// if we can write to a database, then we can read it as well
		ourDatabase = ourHelper.getWritableDatabase();
		return this;
	}

	/**
	 * close database
	 */
	public void close() {
		ourHelper.close();
	}

	/**
	 * 
	 * @param announcement
	 * @return the rowID (same as announcementID)
	 */
	public long createAnnouncementEntry(Announcement announcement) {
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_ANNOUNCEMENTID, announcement.getID());
		cv.put(KEY_ANNOUNCEMENT_CONTENT, announcement.getContent());
		cv.put(KEY_ANNOUNCEMENT_TITLE, announcement.getTitleText());
		cv.put(KEY_ANNOUNCEMENT_TIME, announcement.getDate().getTime());
		cv.put(KEY_ANNOUNCEMENT_EXPIRE, announcement.getExpireDate().getTime());
		cv.put(KEY_ANNOUNCEMENT_TYPE, announcement.getType().ordinal());
		cv.put(KEY_ANNOUNCEMENT_IMPORTANT, announcement.isImportant() ? 1 : 0);
		return ourDatabase.insert(ANNOUNCEMENT_TABLE, null, cv);
	}

	@SuppressWarnings("deprecation")
	public long createSchedEntry(Schedule schedule) {
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_SCHED_START, schedule.getStartTime().getHours() + ":" + schedule.getStartTime().getMinutes());
		cv.put(KEY_SCHED_END, schedule.getEndTime().getHours() + ":" + schedule.getEndTime().getMinutes());
		cv.put(KEY_SCHED_COURSECODE, schedule.getCourse());
		cv.put(KEY_SCHED_DAY, schedule.getDay());
		return ourDatabase.insert(SCHED_TABLE, null, cv);
	}

	public void createAnnouncementEntries(ArrayList<Announcement> announcements) {
		Iterator<Announcement> it = announcements.iterator();
		if (it == null)
			return;
		while (it.hasNext()) {
			createAnnouncementEntry(it.next());
		}
	}

	/**
	 * 
	 * @param after
	 *            fetch all announcements after this; if after is null, then
	 *            just return all announcements
	 * @return all announcements after the parameter {@code after}; if after is
	 *         null, then just return all announcements
	 */
	public ArrayList<Announcement> getAllAnnouncements(Date after) {
		// TODO Auto-generated method stub
		String[] columns = new String[] { KEY_ANNOUNCEMENTID, KEY_ANNOUNCEMENT_CONTENT, KEY_ANNOUNCEMENT_TYPE, KEY_ANNOUNCEMENT_TIME, KEY_ANNOUNCEMENT_EXPIRE, KEY_ANNOUNCEMENT_TITLE,
				KEY_ANNOUNCEMENT_IMPORTANT };

		Cursor c;
		if (after == null) {
			c = ourDatabase.query(ANNOUNCEMENT_TABLE, columns, null, null, null, null, null);
		} else {
			c = ourDatabase.query(ANNOUNCEMENT_TABLE, columns, KEY_ANNOUNCEMENT_TIME + ">" + after.getTime() + " AND " + KEY_ANNOUNCEMENT_EXPIRE + ">" + System.currentTimeMillis(), null, null, null,
					null);
		}
		ArrayList<Announcement> result = new ArrayList<Announcement>();

		/*
		 * //indeces int iID = c.getColumnIndex(KEY_ANNOUNCEMENTID); int
		 * icontent = c.getColumnIndex(KEY_ANNOUNCEMENT_CONTENT); int itype =
		 * c.getColumnIndex(KEY_ANNOUNCEMENT_TYPE); int itime =
		 * c.getColumnIndex(KEY_ANNOUNCEMENT_TIME); int iexpire =
		 * c.getColumnIndex(KEY_ANNOUNCEMENT_EXPIRE); int ititle =
		 * c.getColumnIndex(KEY_ANNOUNCEMENT_TITLE);
		 */
		/*
		 * ArrayList<Announcement> test = new ArrayList<Announcement>();
		 * test.add(new Announcement(0, null, null, "Main 1", "sub 1",
		 * Announcement.AnnouncementType.ACADEMIC, false)); test.add(new
		 * Announcement(0, null, null, "Main 2", "sub 2",
		 * Announcement.AnnouncementType.ADMINISTRATIVE, true)); test.add(new
		 * Announcement(0, null, null, "Main 3", "sub 3",
		 * Announcement.AnnouncementType.FINANCIAL, false)); test.add(new
		 * Announcement(0, null, null, "Main 4", "sub 4",
		 * Announcement.AnnouncementType.REMINDER, false)); test.add(new
		 * Announcement(0, null, null, "Main 5", "sub 5",
		 * Announcement.AnnouncementType.NEWS, false)); test.add(new
		 * Announcement(0, null, null, "Main 6", "sub 6",
		 * Announcement.AnnouncementType.NEWS, true)); test.add(new
		 * Announcement(0, null, null, "Main 7", "sub 7",
		 * Announcement.AnnouncementType.NEWS, true)); test.add(new
		 * Announcement(0, null, null, "Main 8", "sub 8",
		 * Announcement.AnnouncementType.NEWS, false)); test.add(new
		 * Announcement(0, null, null, "Main 9", "sub 9",
		 * Announcement.AnnouncementType.NEWS, false)); test.add(new
		 * Announcement(0, null, null, "Main 10", "sub 10",
		 * Announcement.AnnouncementType.NEWS, false)); return test;
		 */
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			result.add(new Announcement(c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENTID)), new Date(c.getLong(c.getColumnIndex(KEY_ANNOUNCEMENT_TIME))), new Date(c.getLong(c
					.getColumnIndex(KEY_ANNOUNCEMENT_EXPIRE))), c.getString(c.getColumnIndex(KEY_ANNOUNCEMENT_TITLE)), c.getString(c.getColumnIndex(KEY_ANNOUNCEMENT_CONTENT)), AnnouncementType
					.values()[c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENT_TYPE))], (c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENT_IMPORTANT)) == 1)));
		}
		return result;
	}

	public ArrayList<Schedule> getAllSchedules() {
		// TODO Auto-generated method stub
		String[] columns = new String[] { KEY_SCHED_ID, KEY_SCHED_START, KEY_SCHED_END, KEY_SCHED_COURSECODE, KEY_SCHED_DAY };

		Cursor c = ourDatabase.query(SCHED_TABLE, columns, null, null, null, null, null);

		ArrayList<Schedule> result = new ArrayList<Schedule>();
		DateFormat formatter = new SimpleDateFormat("hh:mm");

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			Schedule sched = null;
			try {
				sched = new Schedule(c.getString(c.getColumnIndex(KEY_SCHED_COURSECODE)), c.getString(c.getColumnIndex(KEY_SCHED_DAY)), formatter.parse(c.getString(c
						.getColumnIndex(KEY_SCHED_START))), formatter.parse(c.getString(c.getColumnIndex(KEY_SCHED_END))));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			sched.setID(c.getInt(c.getColumnIndex(KEY_SCHED_ID)));
			result.add(sched);
		}
		return result;
	}

	public int getLastAnnouncementID() {
		String[] columns = new String[] { KEY_ANNOUNCEMENTID };
		Cursor c = ourDatabase.query(ANNOUNCEMENT_TABLE, columns, null, null, null, null, KEY_ANNOUNCEMENTID);
		c.moveToLast();
		if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
			return 0;
		}
		return c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENTID));
	}

	/**
	 * 
	 * @param row
	 *            ID of the announcement
	 * @return announcement from the SQLite database
	 * @throws SQLException
	 */
	public Announcement getAnnouncement(long row) throws SQLException {
		// TODO Auto-generated method stub
		String[] columns = new String[] { KEY_ANNOUNCEMENTID, KEY_ANNOUNCEMENT_CONTENT, KEY_ANNOUNCEMENT_TYPE, KEY_ANNOUNCEMENT_TIME, KEY_ANNOUNCEMENT_EXPIRE, KEY_ANNOUNCEMENT_TITLE,
				KEY_ANNOUNCEMENT_IMPORTANT };

		Cursor c = ourDatabase.query(ANNOUNCEMENT_TABLE, columns, KEY_ANNOUNCEMENTID + "=" + row, null, null, null, null);

		if (c != null) {
			c.moveToFirst();
			return new Announcement(c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENTID)), new Date(c.getLong(c.getColumnIndex(KEY_ANNOUNCEMENT_TIME))), new Date(c.getLong(c
					.getColumnIndex(KEY_ANNOUNCEMENT_EXPIRE))), c.getString(c.getColumnIndex(KEY_ANNOUNCEMENT_TITLE)), c.getString(c.getColumnIndex(KEY_ANNOUNCEMENT_CONTENT)),
					AnnouncementType.values()[c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENT_TYPE))], (c.getInt(c.getColumnIndex(KEY_ANNOUNCEMENT_IMPORTANT)) == 1));
		}
		return null;
	}

	/**
	 * 
	 * @param row
	 *            announcementID
	 * @param announcement
	 *            updated announcement to update to the row {@code lRow}
	 * @throws SQLException
	 */
	public void updateAnnouncementEntry(long row, Announcement announcement) throws SQLException {
		// TODO Auto-generated method stub
		// Announcement ID should be announcement.getID()?
		ContentValues cvUpdate = new ContentValues();
		cvUpdate.put(KEY_ANNOUNCEMENT_CONTENT, announcement.getContent());
		cvUpdate.put(KEY_ANNOUNCEMENT_TITLE, announcement.getTitleText());
		cvUpdate.put(KEY_ANNOUNCEMENT_EXPIRE, announcement.getDate().getTime());
		cvUpdate.put(KEY_ANNOUNCEMENT_EXPIRE, announcement.getExpireDate().getTime());
		cvUpdate.put(KEY_ANNOUNCEMENT_TYPE, announcement.getType().ordinal());
		cvUpdate.put(KEY_ANNOUNCEMENT_IMPORTANT, announcement.isImportant() ? 1 : 0);
		ourDatabase.update(ANNOUNCEMENT_TABLE, cvUpdate, KEY_ANNOUNCEMENTID + "=" + row, null);
	}
	
	public void updateSchedEntry(Schedule schedule) throws SQLException {
		// TODO Auto-generated method stub
		// Announcement ID should be announcement.getID()?
		ContentValues cvUpdate = new ContentValues();
		cvUpdate.put(KEY_SCHED_START, schedule.getStartTime().getHours() + ":" + schedule.getStartTime().getMinutes());
		cvUpdate.put(KEY_SCHED_END, schedule.getEndTime().getHours() + ":" + schedule.getEndTime().getMinutes());
		cvUpdate.put(KEY_SCHED_COURSECODE, schedule.getCourse());
		cvUpdate.put(KEY_SCHED_DAY, schedule.getDay());
		ourDatabase.update(SCHED_TABLE, cvUpdate, KEY_SCHED_ID + "=" + schedule.getID(), null);
	}

	/**
	 * 
	 * @param row
	 *            announcementID
	 * @throws SQLException
	 */
	public void deleteAnnouncementEntry(long row) throws SQLException {
		// TODO Auto-generated method stub
		ourDatabase.delete(ANNOUNCEMENT_TABLE, KEY_ANNOUNCEMENTID + "=" + row, null);
	}
	
	public void deleteSchedEntry(long row) throws SQLException {
		// TODO Auto-generated method stub
		ourDatabase.delete(SCHED_TABLE, KEY_SCHED_ID + "=" + row, null);
	}
}
